﻿namespace dbcntn
{
    public class Query
    {
        #region Select

        public static string OnKayit = 
            "select a.student_id,a.name,b.description as fakulty,c.description as bolum,f.description as cource,d.description as country,e.description as nation,g.description as 'Pol' " +
            "from onmaintable a,fakulty b,bolum c,country d,nation e,cource f,pol g where a.bolum_id=c.id and c.fakulty_id=b.id and a.cource_id=f.id and a.country_id=d.id and a.nation_id=e.id and g.id=a.pol_id ";
        public static string KesinKayit =
            "SELECT a.student_id,a.Name,b.description AS fakulty,c.description AS bolum,f.description AS yurt, e.description AS oda,d.description AS yatak,g.description AS pol1 " +
            "FROM kmaintable a,fakulty b,bolum c,bed d,room e,yurtdef f,pol g WHERE a.Bolum_id=c.ID AND c.fakulty_id=b.ID AND a.bed_id=d.ID AND d.room_id=e.ID AND e.yurt_id=f.ID AND a.Pol_id=g.ID ";
        public static string Fakulty = "SELECT ID,description AS 'Fakulty Name' FROM fakulty";

        public static string Bolum = "SELECT b.id,b.fakulty_id,a.description as 'Fakulty',b.description as 'Bolum' from fakulty a,bolum b where a.id=b.fakulty_id";

        public static string Country = "SELECT ID,description AS 'Country Name' FROM country";

        public static string Nation = "SELECT ID,description AS 'Nation' FROM nation";

        public static string Profession = "SELECT ID,description AS 'Profession' FROM profession";

        public static string Valute = "SELECT ID,description AS 'Valute' FROM valute";

        public static string Place = "select * from place";//"SELECT ID,DESCRIPTION AS 'Place',ball as 'Ball' FROM place";

        public static string Puanortalama = "SELECT * FROM puanortalama";

        public static string Users = "SELECT a.ID,a.priv_id, a.Identity_no, a.password, a.name, a.surname, b.description FROM users a,priv b WHERE a.priv_id=b.ID";

        public static string StudentHandicap= "SELECT * from studenthandicap";

        public static string ParentHandicap = "SELECT * from parenthandicap";

        public static string Cource = "SELECT * from cource";

        public static string Odul = "SELECT * from odul";

        public static string ParentState = "SELECT * from parentstate";

        public static string Yurt = "SELECT * from yurtdef";

        public static string Kardes = "SELECT * from kardes";

        public static string Room = "SELECT  a.ID, b.ID AS Expr1, c.ID AS Expr2, b.description AS Yurt, a.description AS Oda, c.description AS Cinsiyet," +
                                    "" +
                                    "(SELECT COUNT(*) FROM bed WHERE room_id=a.ID) AS 'Yatak sayisi' " +
                                    "FROM room a Left JOIN yurtdef b ON a.yurt_id = b.ID LEFT JOIN pol c ON a.pol_id = c.ID";

        public static string Bed = "SELECT a.id,a.room_id,b.yurt_id,c.description,b.description,a.description from bed a,room b,yurtdef c where a.room_id=b.id and b.yurt_id=c.id ORDER BY b.yurt_id,a.room_id,a.description";

        public static string ImzaCizelgesi =
            "SELECT d.description AS Yurt,c.description AS Oda,  b.description AS Yatak,a.student_id, a.Name " +
            "FROM kmaintable a RIGHT JOIN bed b ON a.bed_id = b.ID INNER JOIN room c ON b.room_id = c.ID " +
            "INNER JOIN yurtdef d ON d.ID = c.yurt_id INNER JOIN pol ON pol.ID = c.Pol_id";

        public static string OrderBy = "ORDER BY d.description,c.Description,b.ID";

        public static string MaddeNo = "select id,description as 'Madde no' from maddeno";

        public static string MaddeBend = "select a.id,a.maddeno_id,b.description as 'Madde no',a.description as 'Bend',a.maddetext as 'Madde Text' from maddebend a,maddeno b where a.maddeno_id=b.id";

        public static string Disiplin = "select id,maddeno,maddebend,maddetext,description as 'Aciklama',ceza,cast(date(date_reg) as char) as Date from disiplin";

        public static string Arsiv =
            "select a.donem," +
            "" +
            "a.student_id,a.name,b.description as fakulty,c.description as bolum,f.description as cource,d.description as country,e.description as nation,a.regdate from arsiv a,fakulty b,bolum c,country d,nation e,cource f where a.bolum_id=c.id and c.fakulty_id=b.id and a.cource_id=f.id and a.country_id=d.id and a.nation_id=e.id";

        public static string Izin = "select * from izin";

        public static string Izinsiz = "select * from izinsiz";

        public static string Hazirlik = "select * from hazirlik";

        #endregion

        #region Insert



        #endregion
    }
}
